import { DBMgr } from "./db";
import * as fs from "fs";
import * as path from "path";
import * as XLSX from 'xlsx';
import { dateFormat, findTopThreeFrequentSubstrings } from "./tools";
import { ENV } from "../cofing";

export class DataExporter {
    static ins: DataExporter;

    async findUsers(argv:{chapter:string},select:any){
        let where =  argv.chapter?[{"chapter":argv.chapter  }, { "removed": 0 }] :[{ "removed": 0 }]
        let cond = { "where": { AND:where }}

        // if(select){
        //     cond["select"] = select;
        // }
        let res = await DBMgr.ins.prisma.t_user.findMany(cond)
        return res;
    }

    /**
     * 
     * @param argv 是否要删除的数据
     */
    async exportToExcel(argv: any) {
        // argv = argv || { "removed": 0 }
        // let res = await DBMgr.ins.prisma.t_sunchart.findMany({ "where": { AND: [{ "val2": { "not": "" } }, argv] } })

        // console.log(res);


        let users = await this.findUsers(argv,{id:1});

        let uids = users.map((item:{id:number})=>{
            return item.id
        })

        let requirement = await this.exportRequirement(uids);
        let supply = await this.exportSupply(uids);
        let match = await this.exportMatch(uids);
        let around = await this.exportAround(uids);


        let rawData = await this.exportRaw(uids);

        let header = rawData.shift();
        let rawRows = rawData.map((value)=>{
            let row:any = {};
            header.forEach((k,idx)=>{
                row[k] = value[idx]
            })
            return row
        })

        let data = {核心客户:requirement, 身边资源:supply, 会内联系范围:match,潜在外部协力:around,rawData:rawRows};
        let filePath = this.saveToFile(data);

        return {filePath,data};
    }

    saveToFile(tables:any) {

        // const sheet1Data = [
        //     { id: 1, name: "Item 1", category: "Category A" },
        //     { id: 2, name: "Item 2", category: "Category B" },
        //     // 更多条目...
        //   ];

        // 创建 workbook 对象
        const wb = XLSX.utils.book_new();

        let total:any[] = []

        let blacklist = ["1","0","%","(","（",")","）","2","3","6","7","8","9"]

        Object.keys(tables).forEach((tableKey)=>{
            if(tableKey == "rawData"){
                return;
            }
            const sheetData = tables[tableKey];
            let list = sheetData.map((item:any)=>{
                return item.行业
            })
            let res2 = findTopThreeFrequentSubstrings(list,2,blacklist) as any;
            res2.forEach((item:any)=>{
                item.sheet = tableKey;
                total.push(item)
            })
            let res3 = findTopThreeFrequentSubstrings(list,3,blacklist) as any;
            res3.forEach((item:any)=>{
                item.sheet = tableKey;
                total.push(item)
            })
            let res4 = findTopThreeFrequentSubstrings(list,4,blacklist) as any;
            res4.forEach((item:any)=>{
                item.sheet = tableKey;
                total.push(item)
            })
            
            const ws = XLSX.utils.json_to_sheet(sheetData);
            XLSX.utils.book_append_sheet(wb, ws, tableKey);
        })

        const ws = XLSX.utils.json_to_sheet(total);
        XLSX.utils.book_append_sheet(wb, ws, "统计");

        const rawDataSheet = XLSX.utils.json_to_sheet(tables.rawData);
        XLSX.utils.book_append_sheet(wb, rawDataSheet, "原始数据");

        // 写入 Excel 文件
        // const writeOpts = ;
        const excelBuffer = XLSX.write(wb, { bookType: 'xlsx', type: 'buffer' });

        return this.writeFile(excelBuffer);
        // // 将缓冲区保存到文件
        // let exportDir = "public";
        // if(!fs.existsSync(exportDir)){
        //     fs.mkdirSync(exportDir,{recursive:true})
        // }

        // let filePath = "太阳图数据"+dateFormat()+".xlsx"

        // fs.writeFileSync(path.join(exportDir,filePath), excelBuffer);

        // return filePath

    }

    writeFile(buffer:any){
        // 将缓冲区保存到文件
        const exportDir = process.env.EXPORT_FILE_PATH
 
        if(!fs.existsSync(exportDir)){
            fs.mkdirSync(exportDir,{recursive:true})
        }

        let filePath = "sundata_"+dateFormat()+".xlsx"

        fs.writeFileSync(path.join(exportDir,filePath), buffer);

        return filePath
    }

    exportTable(list: any[], colname: string) {
        let lines: any[] = []
        for (let i = 0; i < list.length; i++) {
            let row = list[i]
            let uname = row.uname+"|"+row.val1
            let values = row[colname].replaceAll("，", ",").replaceAll("、", ",").replaceAll("\n", " ").split(",")
            values.forEach((v: any) => {
                v = v.trim()
                if(v){
                    lines.push({会员:uname, 行业:v})
                }
            })
        }

        console.log("-------", colname);
        console.log(lines);

        return lines;
    }

    //全表拆分为：需求表，资源表，匹配表
    async exportRequirement(uids:number[]) {
        let res = await DBMgr.ins.prisma.t_sunchart.findMany( { "where": { AND: [{uid:{"in":uids}},{ "val2": { "not": "" } }, { "removed": 0 }] } })

        return this.exportTable(res, "val2")
        // let res = await DBMgr.ins.prisma.t_sunchart.findMany({"where":{"val5":{"not":""}}})
    }
    async exportSupply(uids:number[]) {
        let res = await DBMgr.ins.prisma.t_sunchart.findMany({ "where": { AND: [{uid:{"in":uids}},{ "val3": { "not": "" } }, { "removed": 0 }] }})
        return this.exportTable(res, "val3")
    }
    async exportMatch(uids:number[]) {
        let res = await DBMgr.ins.prisma.t_sunchart.findMany({ "where": { AND: [{uid:{"in":uids}},{ "val4": { "not": "" } }, { "removed": 0 }] } })
        return this.exportTable(res, "val4")
    }
    async exportAround(uids:number[]) {
        let res = await DBMgr.ins.prisma.t_sunchart.findMany({ "where": { AND: [{uid:{"in":uids}},{ "val5": { "not": "" } }, { "removed": 0 }] } })
        return this.exportTable(res, "val5")
    }

    
    /**
     * 
     * @param argv 是否要删除的数据
     */
    async exportRawToExcel(argv: any) {
        // argv = argv || { "removed": 0 }
        // let res = await DBMgr.ins.prisma.t_sunchart.findMany({ "where": { AND: [{ "val2": { "not": "" } }, argv] } })

        // console.log(res);


        let users = await this.findUsers(argv,{id:1});

        let uids = users.map((item:{id:number})=>{
            return item.id
        })

        let data = await this.exportRaw(uids);

        let filePath = this.saveRawToFile(data);

        return {filePath,data};
    }

    async exportRaw(uids:number[]){
        let res = await DBMgr.ins.prisma.t_sunchart.findMany({ "where": { AND: [{uid:{"in":uids}},
            // { "val5": { "not": "" } },
            { "removed": 0 }
        ] } })

        let list = [["名称","状态","行业","TOP3客户","拥有的资源","分会有关行业","共同客户未进分会"]]
        for(let row of res){
            let columns = ["uname","removed","val1","val2","val3","val4","val5"];

            let result:any[] = []
            for(let k of columns){

                let str = (row as any)[k]||""
                if(typeof(str)=="string"){
                    result.push(str.replace(/，/g, ",").replace(/、/g, ",").replace(/\n/g, ","))
                }else{
                    result.push(str);
                }
            }
            list.push(result);
        }

        return list;
        // return this.exportTable(res, "val5")
    }

    saveRawToFile(params:any) {
        let sheetData = params;

        const wb = XLSX.utils.book_new();
        const ws = XLSX.utils.json_to_sheet(sheetData);
        let tableKey = "export"
        XLSX.utils.book_append_sheet(wb, ws, tableKey);
        const excelBuffer = XLSX.write(wb, { bookType: 'xlsx', type: 'buffer' });

        return this.writeFile(excelBuffer);
    }

}

DataExporter.ins = new DataExporter();